package unit;

import myenum.ParamEnum;

import java.io.File;
import java.sql.*;

public final class SqliteClass {
    private Connection connection;
    private Statement statement;
    /**
     * 全路径Sqlite3数据库文件名
     */
    private String dbName;

    @Override
    public void finalize() throws Throwable {
        super.finalize();
        connection.close();
    }

    /**
     * @param dbName 全路径Sqlite数据库文件名
     * @throws SQLException
     */
    public SqliteClass(String dbName) throws SQLException {
        ini(dbName);
    }

    public SqliteClass() throws SQLException {
        dbName = "D:"
                + File.separator
                + "DoorLocalDB.db";
        ini(dbName);
    }

    private void ini(String dbName) throws SQLException {
        connection = DriverManager.getConnection(String.format("jdbc:sqlite:%s", dbName));
        statement = connection.createStatement();
    }

    /**
     * 执行Sql，得到查询结果
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public ResultSet getSqlTable(String sql) throws SQLException {
        return statement.executeQuery(sql);
    }

    public boolean executeSql(String sql) throws SQLException {
        return statement.execute(sql);
    }

    public int updateSql(String sql) throws SQLException {
        return statement.executeUpdate(sql);
    }

    public int insertSql(String sql) throws SQLException {
        return updateSql(sql);
    }

    public int deleteSql(String sql) throws SQLException {
        return updateSql(sql);
    }

    /**
     * 清空指定表中所有的数据
     *
     * @param tableName 指定表名
     * @return 影响条件
     * @throws SQLException
     */
    public int clearTable(String tableName) throws SQLException {
        String sql = String.format("delete from %s", tableName);
        return updateSql(sql);
    }

    private String getSqliteParam(String id) throws SQLException {
        String sql = String.format("select param from inf_EquipmentParam where id='%s'"
                , id);
        String result = null;
        final ResultSet sqlTable = getSqlTable(sql);
        while (sqlTable.next()) {
            final String param = sqlTable.getString("param");
            if (param != null) {
                result = param;
                break;
            }
        }
        return result;
    }

    public String getPASS_WORD() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.PASS_WORD.getParamId());
        return getParam(sqliteParam, String.class);
    }

    public String getAI_NAME() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.AI_NAME.getParamId());
        return getParam(sqliteParam, String.class);
    }

    public float getAI_DOOR_VALUE() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.AI_DOOR_VALUE.getParamId());
        return getParam(sqliteParam, Float.class);
    }

    public int getAUTO_OPENDOOR_TIME() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.AUTO_OPENDOOR_TIME.getParamId());
        return getParam(sqliteParam, Integer.class);
    }

    public int getTAKE_OPENDOOR_TIME() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.TAKE_OPENDOOR_TIME.getParamId());
        return getParam(sqliteParam, Integer.class);
    }

    public int getWITH_OPENDOOR_TIME() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.WITH_OPENDOOR_TIME.getParamId());
        return getParam(sqliteParam, Integer.class);
    }

    public String getURL_ADDRESS_LOG() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.URL_ADDRESS_LOG.getParamId());
        return getParam(sqliteParam, String.class);
    }

    public int getPAGE_SIZE() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.PAGE_SIZE.getParamId());
        return getParam(sqliteParam, Integer.class);
    }

    public String getURL_ADDRESS_FRONT() throws SQLException {
        final String sqliteParam = getSqliteParam(ParamEnum.URL_ADDRESS_FRONT.getParamId());
        return getParam(sqliteParam, String.class);
    }

    private <T> T getParam(String param, Class<T> tClass) {
        if (tClass == null || param == null) {
            return null;
        }
        T result;
        String s = tClass.getName();
        if ("java.lang.Integer".equals(s)) {
            result = (T) Integer.valueOf(param);
        } else if ("java.lang.Boolean".equals(s)) {
            result = (T) Boolean.valueOf(param);
        } else if ("java.lang.Float".equals(s)) {
            result = (T) Float.valueOf(param);
        } else if ("java.util.Date".equals(s)) {
            result = (T) Date.valueOf(param);
        } else {
            result = (T) param;
        }
        return result;
    }
}
